﻿using System;
using System.Data;

//using System.Linq;
//using System.Web;
//using System.Web.Security;
//using System.Web.UI;
//using System.Web.UI.HtmlControls;
//using System.Web.UI.WebControls;
//using System.Web.UI.WebControls.WebParts;
//using System.Xml.Linq;
using System.Data.SqlClient;

namespace common
{
    public class SqlServerHelper
    {
        public static string strConnString = "";

        public static bool TestSqlConnstring(string strConnection)
        {
            try
            {
                SqlConnection con = new SqlConnection(strConnection);
                con.Open();
                DataTable dt = con.GetSchema("tables");
                con.Close();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        protected static string GetConnString()
        {
            if (strConnString != "")
            {
                return strConnString;
            }

            string connectionString = "server=localhost;database=test;uid=sa;pwd=;";
            return connectionString;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection con = new SqlConnection(GetConnString());
            con.Open();
            SqlCommand com = new SqlCommand();

            com.Connection = con;
            com.CommandText = sql;

            SqlDataReader thisReader = com.ExecuteReader();

            return thisReader;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            SqlConnection con = new SqlConnection(GetConnString());
            SqlCommand com = new SqlCommand();

            com.Connection = con;
            com.CommandText = sql;
            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = com;
            DataSet ds = new DataSet();
            ada.Fill(ds);
            com.Dispose();

            DataTable dt = new DataTable();
            dt = ds.Tables[0];
            return dt;
        }

        public static int ExecuteNonQuery(string Query)
        {
            int Result;

            SqlConnection con = new SqlConnection(GetConnString());
            con.Open();
            SqlCommand com = new SqlCommand(Query, con);
            Result = com.ExecuteNonQuery();
            con.Close();
            return Result;
        }

        public static object ExecuteAlone(string Query)
        {
            object Result;

            SqlConnection con = new SqlConnection(GetConnString());
            con.Open();

            SqlCommand com = new SqlCommand(Query, con);
            Result = com.ExecuteScalar();
            con.Close();

            return Result;
        }
    }
}